package com.example.todo;

import java.sql.Date;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter {
	// For logging:
	private static final String TAG = "DBAdapter";
	
	// Database Fields
	public static final String KEY_ROWID = "_id";
	public static final int COL_ROWID = 0;
	public static final String KEY_TASK_NAME = "name";
	public static final String KEY_CATEGORY = "category";
	public static final String KEY_SUBJECT = "subject";
	public static final String KEY_PROJECT = "project";
	public static final String KEY_DATE = "date";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_LOCATION = "location";	
	//Field numbers
	public static final int COL_TASK_NAME = 1;
	public static final int COL_CATEGORY = 2;
	public static final int COL_SUBJECT = 3;
	public static final int COL_PROJECT = 4;
	public static final int COL_DATE = 5;
	public static final int COL_DESCRIPTION = 6;
	public static final int COL_LOCATION = 7;
	
	public static final String[] ALL_KEYS = new String[] {KEY_ROWID, KEY_TASK_NAME, KEY_CATEGORY, 
		KEY_SUBJECT,KEY_PROJECT,KEY_DATE, KEY_DESCRIPTION,KEY_LOCATION};
	
	// Database name and table name.
	public static final String DATABASE_NAME = "MyDb";
	public static final String DATABASE_TABLE = "mainTable";
	// Track DB version if a new version of app changes the format.
	public static final int DATABASE_VERSION = 4;	
	//Build the foundations of the table
	private static final String DATABASE_CREATE_SQL = 
			"create table " + DATABASE_TABLE 
			+ " (" + KEY_ROWID + " integer primary key autoincrement, "
			+ KEY_TASK_NAME + " text not null, "
			+ KEY_CATEGORY + " integer not null, "
			+ KEY_SUBJECT + " text, "
			+ KEY_PROJECT+ " text, "
			+ KEY_DATE+ " text, "
			+ KEY_DESCRIPTION+ " text, "
			+ KEY_LOCATION+ " text "			
			+ ");";
	
	private final Context context;
	private DatabaseHelper myDBHelper;
	private SQLiteDatabase db;

	//	Public methods:
	public DBAdapter(Context ctx) {
		this.context = ctx;
		myDBHelper = new DatabaseHelper(context);
	}
	
	// Open the database connection.
	public DBAdapter open() {
		db = myDBHelper.getWritableDatabase();
		return this;
	}
	
	// Close the database connection.
	public void close() {
		myDBHelper.close();
	}
	
	// Add a new set of values to the database.
	public long insertRow(String taskName, String category, 
			String subject, String project, String date,
			String description, String location) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_TASK_NAME, taskName);
		initialValues.put(KEY_CATEGORY, category);
		initialValues.put(KEY_SUBJECT, subject);
		initialValues.put(KEY_PROJECT, project);
		initialValues.put(KEY_DATE, date);
		initialValues.put(KEY_DESCRIPTION, description);
		initialValues.put(KEY_LOCATION, location);	
		// Insert it into the database.
		return db.insert(DATABASE_TABLE, null, initialValues);
	}
	
	// Delete a row from the database, by rowId (primary key)
	public boolean deleteRow(long rowId) {
		String where = KEY_ROWID + "=" + rowId;
		return db.delete(DATABASE_TABLE, where, null) != 0;
	}
	
	// Delete entire database contents
	public void deleteAll() {
		Cursor c = getAllRows();
		long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
		if (c.moveToFirst()) {
			do {
				deleteRow(c.getLong((int) rowId));				
			} while (c.moveToNext());
		}
		c.close();
	}
	
	// Return all data in the database.
	public Cursor getAllRows() {
		String where = null;
		Cursor c = 	db.query(true, DATABASE_TABLE, ALL_KEYS, 
							where, null, null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	
	//Sort
	public Cursor sort(String column){
		Cursor c = db.rawQuery("SELECT * FROM mainTable ORDER BY name", null);
		return c;
	}

	// Get a specific row by category 
	public Cursor getRow_Category(String category) {
		Cursor c = db.rawQuery("SELECT * FROM mainTable WHERE category = '"+category+"'", null);
		return c;
	}
	
	public Cursor getRow_Filter(String query){
		//make sure the argument has contents
		if (query.length() > 31){
			Cursor c = db.rawQuery(query, null);
			return c;
			}
		else{
			String where = null;
			Cursor c = 	db.query(true, DATABASE_TABLE, ALL_KEYS, 
								where, null, null, null, null, null);
			if (c != null) {
				c.moveToFirst();
			}
			return c;
		}
	}
	
	// Get a specific row (by rowId)
	public Cursor getRow(long rowId) {
		String where = KEY_ROWID + "=" + rowId;
		Cursor c = 	db.query(true, DATABASE_TABLE, ALL_KEYS, 
						where, null, null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	
	// Change an existing row to be equal to new data.
	public boolean updateRow(long rowId, String taskName, String category, String subject, String project, String date,
			String description, String location) {
		String where = KEY_ROWID + "=" + rowId;
		// Create row's data:
		ContentValues newValues = new ContentValues();
		newValues.put(KEY_TASK_NAME, taskName);
		newValues.put(KEY_CATEGORY, category);
		newValues.put(KEY_SUBJECT, subject);
		newValues.put(KEY_PROJECT, project);
		newValues.put(KEY_DATE, date);
		newValues.put(KEY_DESCRIPTION, description);
		newValues.put(KEY_LOCATION, location);
		// Insert it into the database.
		return db.update(DATABASE_TABLE, newValues, where, null) != 0;
	}
		
	//	Private Helper Classes:
	//Private class which handles database creation and upgrading.
	//Used to handle low-level database access.
	private static class DatabaseHelper extends SQLiteOpenHelper
	{
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DATABASE_CREATE_SQL);			
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading application's database from version " + oldVersion
					+ " to " + newVersion + ", which will destroy all old data!");
			
			// Destroy old database:
			_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			
			// Recreate new database:
			onCreate(_db);
		}
	}
}
